﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Skynet.BusinessLogic.DataAccess;
using Microsoft.Practices.Unity;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
using System.Configuration;
using System.Threading;
using System.Data;
using System.Xml;
namespace Skynet.BusinessLogic.DataAccess
{
    public class ExamCategoryADORepository : IExamCategoryRepository
    {
        
        private string ConnectionString; 
        public ExamCategoryADORepository(string ConnectionString)
        {
            this.ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString;          
        }

        public void Add(BusinessEntities.ExamCategory item)
        {
            SqlConnection Connection = null; 
            try
            {
                Connection = new SqlConnection(this.ConnectionString); 
                var cmd = new SqlCommand("insert into ExamCategories (id, title, description) values(@id,@Title,@Description)", Connection);
                cmd.Parameters.AddWithValue("@id", item.Id);
                cmd.Parameters.AddWithValue("@Title", item.Title);
                cmd.Parameters.AddWithValue("@Description", item.Description);                
                Connection.Open();
                cmd.ExecuteNonQuery();        
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;                 
            }
            finally
            {
                if (Connection !=  null)  
                    Connection.Close();
            }
        }

        public void Add(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            SqlConnection Connection = null;  
            SqlCommand cmd = null; 
            try
            {
                Connection = new SqlConnection(this.ConnectionString);
                cmd = new SqlCommand("insert into ExamCategories (id, title, description) values(@id,@Title,@Description)", Connection);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier);
                cmd.Parameters.Add("@Title", System.Data.SqlDbType.VarChar);
                cmd.Parameters.Add("@Description", System.Data.SqlDbType.VarChar);                
                Connection.Open();                
                cmd.Transaction = Connection.BeginTransaction();
                foreach (var item in items)
                {
                    cmd.Parameters["@Id"].Value = item.Id;
                    cmd.Parameters["@Title"].Value = item.Title;
                    cmd.Parameters["@Description"].Value = item.Description;                    
                    cmd.ExecuteNonQuery();                    
                }
                cmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (cmd != null && cmd.Transaction != null)
                {
                    try
                    {
                        cmd.Transaction.Rollback();
                    }
                    catch (Exception dex)
                    {
                        ExceptionPolicy.HandleException(dex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);                        
                    }                    
                }
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {

                if (Connection != null)
                    Connection.Close();
            }
        }

        public void Update(BusinessEntities.ExamCategory item)
        {
            SqlConnection Connection = null; 
            SqlCommand cmd = null;
            try
            {
                Connection = new SqlConnection(this.ConnectionString); 
                cmd = new SqlCommand("update ExamCategories set Title = @title, Description=@Description where id =@id;", Connection);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier).Value = item.Id;
                cmd.Parameters.Add("@Title", System.Data.SqlDbType.VarChar).Value = item.Title ;
                cmd.Parameters.Add("@Description", System.Data.SqlDbType.VarChar).Value = item.Description;
                Connection.Open();
                cmd.ExecuteNonQuery(); 
            }
            catch (Exception ex)
            {                
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (Connection != null)
                    Connection.Close();
            }
        }

        public void Update(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            SqlCommand cmd = null;
            SqlConnection Connection = null;  
            try
            {
                Connection = new SqlConnection(this.ConnectionString);  
                cmd = new SqlCommand("update ExamCategories set Title = @title, Description=@Description where id =@id;", Connection);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier);
                cmd.Parameters.Add("@Title", System.Data.SqlDbType.VarChar);
                cmd.Parameters.Add("@Description", System.Data.SqlDbType.VarChar);
                Connection.Open();
                cmd.Transaction = Connection.BeginTransaction();
                foreach (var item in items)
                {
                    cmd.Parameters["@Id"].Value = item.Id;
                    cmd.Parameters["@Title"].Value = item.Title;
                    cmd.Parameters["@Description"].Value = item.Description;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (cmd != null && cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();
                }
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (Connection != null)
                    Connection.Close();
            }
        }

        public void Delete(BusinessEntities.ExamCategory item)
        {
            SqlCommand cmd = null;
            SqlConnection Connection = null; 
            try
            {
                Connection = new SqlConnection(this.ConnectionString);  
                cmd = new SqlCommand("delete ExamCategories where id =@id;", Connection);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier).Value = item.Id;                
                Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (Connection != null)
                    Connection.Close();
            }
        }

        public void Delete(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            SqlCommand cmd = null;
            SqlConnection Connection = null;  
            try
            {
                Connection = new SqlConnection(this.ConnectionString);  
                cmd = new SqlCommand("delete ExamCategories where id =@id;", Connection);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier);                
                Connection.Open();
                cmd.Transaction = Connection.BeginTransaction();
                foreach (var item in items)
                {
                    cmd.Parameters["@Id"].Value = item.Id;                
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (cmd != null && cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();
                }
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (Connection != null)
                    Connection.Close();
            }
        }

        public BusinessEntities.ExamCategory GetOne(object key)
        {
            SqlConnection Connection = null;  
            SqlCommand cmd = null;
            SqlDataReader reader = null;  
            BusinessEntities.ExamCategory result = null;  
            try
            {
                Connection = new SqlConnection(ConnectionString); 
                cmd = new SqlCommand("select * from ExamCategories where id = @id;", Connection);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier).Value = (Guid)key;
                Connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow);
                var idIndex = reader.GetOrdinal("Id"); 
                var titleIndex = reader.GetOrdinal("Title"); 
                var descriptionIndex =  reader.GetOrdinal("Description");  
                var optimisticIndex =  reader.GetOrdinal("OptimisticId");  
                if ( reader.Read())
                {
                    result = new BusinessEntities.ExamCategory();
                    result.Id = reader.GetGuid(idIndex);  
                    result.Title = reader.GetString(titleIndex); 
                    result.Description  = reader.GetString(descriptionIndex);  
                    if (!reader.IsDBNull(optimisticIndex))
                        result.OptimisticId = reader.GetGuid(optimisticIndex); 
                }
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (Connection != null)
                    Connection.Close();
            }
            return result;  
        }

        public IEnumerable<BusinessEntities.ExamCategory> GetAll()
        {
            SqlConnection Connection = null; 
            SqlCommand cmd = null;
            SqlDataReader reader = null; 
            List<BusinessEntities.ExamCategory> results = new List<BusinessEntities.ExamCategory>(); 
            try
            {
                Connection = new SqlConnection(this.ConnectionString);  
                cmd = new SqlCommand("select * from ExamCategories", Connection);               
                Connection.Open();
                reader =  cmd.ExecuteReader(CommandBehavior.SingleResult);
                 var idIndex = reader.GetOrdinal("Id");
                 var titleIndex = reader.GetOrdinal("Title");
                 var descriptionIndex = reader.GetOrdinal("Description");
                 var optimisticIndex = reader.GetOrdinal("OptimisticId");
                 while (reader.Read())
                 {
                     var result = new BusinessEntities.ExamCategory();
                     result.Id = reader.GetGuid(idIndex);
                     result.Title = reader.GetString(titleIndex);
                     result.Description = reader.GetString(descriptionIndex);
                     if (!reader.IsDBNull(optimisticIndex))
                         result.OptimisticId = reader.GetGuid(optimisticIndex);
                     results.Add(result);  
                 }

            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();   
                }
                if (Connection != null)
                {
                    Connection.Close();                    
                }                    
            }
            return results; 
        }
        public System.Data.IDataReader GetAllStream()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            SqlConnection Connection = null; 
            try
            {
                Connection = new SqlConnection(this.ConnectionString);  
                cmd = new SqlCommand("select * from ExamCategories", Connection);                
                Connection.Open();
                reader = cmd.ExecuteReader( System.Data.CommandBehavior.SingleResult | System.Data.CommandBehavior.CloseConnection);                
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }            
            return reader;  
        }


        public System.Data.DataTable GetTable()
        {
            SqlCommand cmd = null;
            var result = new DataTable("ExamCategories");
            
            SqlConnection Connection = null;
            try
            {
                SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(this.ConnectionString);
                
                
                

                Connection = new SqlConnection(this.ConnectionString);
                cmd = new SqlCommand("select * from ExamCategories", Connection);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);


                
                


            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally {
                if (Connection != null)
                    Connection.Close(); 
            }
            return result;  
        }
        public void UpdateTable(DataTable dt)
        {
            SqlConnection Connection = null;
            SqlTransaction Tran = null; 
            try
            {
                Connection = new SqlConnection(this.ConnectionString);
                var SelectCommand = new SqlCommand("usp_ExamCategories_GetAll", Connection);
                SelectCommand.CommandType = CommandType.StoredProcedure;

                var InsertCommand = new SqlCommand("usp_ExamCategories_Insert", Connection);
                InsertCommand.CommandType = CommandType.StoredProcedure; 
                InsertCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier, 0, "Id");
                InsertCommand.Parameters.Add("@Title", SqlDbType.VarChar, 500, "Title");
                InsertCommand.Parameters.Add("@Description", SqlDbType.VarChar, 4000, "Description");


                var UpdateCommand = new SqlCommand("usp_ExamCategories_Update", Connection);
                UpdateCommand.CommandType = CommandType.StoredProcedure; 
                UpdateCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier, 0, "Id");
                UpdateCommand.Parameters.Add("@Title", SqlDbType.VarChar, 500, "Title");
                UpdateCommand.Parameters.Add("@Description", SqlDbType.VarChar, 4000, "Description");


                var DeleteCommand = new SqlCommand("usp_ExamCategories_Delete", Connection);
                DeleteCommand.CommandType = CommandType.StoredProcedure;  
                DeleteCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier, 0, "Id");


                SqlDataAdapter adp = new SqlDataAdapter(SelectCommand);
                adp.AcceptChangesDuringUpdate = true;
                adp.InsertCommand = InsertCommand;
                adp.UpdateCommand = UpdateCommand;
                adp.DeleteCommand = DeleteCommand;


                Connection.Open();

                Tran = Connection.BeginTransaction();

                adp.InsertCommand.Transaction = Tran;
                adp.UpdateCommand.Transaction = Tran;
                adp.DeleteCommand.Transaction = Tran;

                adp.UpdateBatchSize = 20;

                adp.Update(dt);  
                
                Tran.Commit();
            }
            catch (Exception ex)
            {
                if (Tran != null)
                    Tran.Rollback();
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally {
                if ( Connection !=null)
                    Connection.Close();
            }
        }


        public void Maintenance(BusinessEntities.ExamCategory item)
        {
            throw new NotImplementedException();
        }
    }
}
